/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac4.servidor;

import edu.uoc.tdp.pac4.beans.Client;
import edu.uoc.tdp.pac4.beans.Mecanic;
import edu.uoc.tdp.pac4.beans.Peca;
import edu.uoc.tdp.pac4.beans.Reparacio;
import edu.uoc.tdp.pac4.beans.ReparacioMecanic;
import edu.uoc.tdp.pac4.beans.Solicitud;
import edu.uoc.tdp.pac4.beans.SolicitudDetall;
import edu.uoc.tdp.pac4.beans.StockPeca;
import edu.uoc.tdp.pac4.beans.Vehicle;
import edu.uoc.tdp.pac4.comun.GestorReparacionesInterface;
import edu.uoc.tdp.pac4.comun.PAC4Exception;
import edu.uoc.tdp.pac4.i18n.TDSLanguageUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

/**
 *
 * @author rfebrerg
 */
public class GestorReparacionesImpl   extends UnicastRemoteObject implements GestorReparacionesInterface, Serializable {
    
    private static final long serialVersionUID = 1L;
    
    public Connection conexion;
    final String propertiesFile = new File("").getAbsolutePath() + "//src//properties///configuration.properties";
    /* Linux Config */
    //final String propertiesFile = new File("").getAbsolutePath() + "/src/properties/configuration.properties";
    final String driver = "org.postgresql.Driver";
    
    
    public GestorReparacionesImpl() throws PAC4Exception,RemoteException, ExceptionBD {
        conectaBD();
    
    }

    public GestorReparacionesImpl(int paramInt) throws PAC4Exception,RemoteException, ExceptionBD {
        super(paramInt);
        conectaBD();
    }

    
    private void conectaBD() throws PAC4Exception,ExceptionBD {
        try {
            Class.forName("org.postgresql.Driver");
            Properties localProperties = new Properties();
            localProperties.load(new FileInputStream(this.propertiesFile));
            String str1 = localProperties.getProperty("url");
            String str2 = localProperties.getProperty("username");
            String str3 = localProperties.getProperty("password");
            
            
            this.conexion = DriverManager.getConnection(str1, str2, str3);
        } catch (ClassNotFoundException localClassNotFoundException) {
            //Servidor.diplay(localClassNotFoundException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.JDBCKO"));
            
        } catch (FileNotFoundException localFileNotFoundException) {
           // Servidor.diplay(localFileNotFoundException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.ConfigurationKO"));
        } catch (IOException localIOException) {
            // Servidor.diplay(localIOException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.PropertiesKO"));
        } catch (SQLException localSQLException) {
            // Servidor.diplay(localSQLException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.BBDDKO"));
        } catch (Exception localException) {
           // Servidor.diplay(localException.getMessage());
            throw new ExceptionBD(localException.getMessage());
        }
    }

     @Override
    public ReparacioMecanic getDetallReparacio(int id) throws PAC4Exception,RemoteException, SQLException {
          ReparacioMecanic r = new ReparacioMecanic();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
           String str ="SELECT *"+
                   // "\"OrdreReparacio\",\"Comptador\",\"Observacions\",\"DataAssignacio\",\"DataInici\",\"DataFi\",Ret.\"Estado\","+
                    //" V.\"marca\",V.\"tipus\",V.\"num_chasis\",V.\"model\",V.\"matricula\",V.\"color\",V.\"any\" "+
                    " FROM \"Reparacio\" as R"+
                    " INNER JOIN \"ReparacionEstadoTipo\" as Ret on Ret.\"IdReparacionEstadoTipo\"=R.\"IdReparacionEstadoTipo\" "+
                    " INNER JOIN \"Solicitud\" as S on S.\"numSol\"=R.\"numSol\" "+
                    "INNER JOIN \"Client\" as C on C.\"nif\"=s.\"client\" " +
                    " INNER JOIN \"Vehicle\" as V on V.\"IdVehicle\"=C.\"IdVehicle\" "+
                    " WHERE \"OrdreReparacio\"= "+id;
            
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
         
                Reparacio re = new Reparacio();
                re.setOrdreReparacio(result.getInt("OrdreReparacio"));
                re.setComptador(result.getDouble("Comptador"));                
                re.setObservacions(result.getString("Observacions"));
                re.setIdMecanic(result.getInt("IdMecanic"));
                re.setEstado(result.getString("Estado"));
                re.setDataAssignacio(result.getDate("DataAssignacio"));
                re.setDataFi(result.getDate("DataFi"));
                re.setDataInici(result.getDate("DataInici"));
                r.setReparacio(re);
                 Vehicle ve=new Vehicle();
                 ve.setMarca(result.getString("marca"));
                 ve.setTipus(result.getString("tipus"));
                 ve.setNum_chasis(result.getString("num_chasis"));
                 ve.setModel(result.getString("model"));
                 ve.setMatricula(result.getString("matricula"));
                 ve.setColor(result.getString("color"));
                 ve.setAnio(result.getDate("any"));
                r.setVehicle(ve);
                Client cli =new Client();
                cli.setNom(result.getString("nom"));
                cli.setCognoms(result.getString("cognoms"));
                cli.setNif(result.getString("nif"));
                r.setCliente(cli);
                r.setStockPeces(new ArrayList<StockPeca>());
        
            }
            return r;
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
           
    }

    @Override
    public ArrayList<Mecanic> getMecanics() throws PAC4Exception,RemoteException, SQLException {
        
        
 ArrayList<Mecanic> m = new ArrayList<Mecanic>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
           String str ="SELECT U.\"ID\",U.\"nom\",U.\"cognom1\" " +
                       " FROM \"Usuari\" As U" +
                       " WHERE  U.\"perfil\"=3 ";
            
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
         
                Mecanic me = new Mecanic();
                me.setIdMecanic(result.getInt("ID"));
                me.setNombre(result.getString("nom")+" "+ result.getString("cognom1"));                
                
                m.add(me);
        
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return m;   
    }

    @Override
    public ArrayList<ReparacioMecanic> getReparacions() throws PAC4Exception,RemoteException, SQLException {
        ArrayList<ReparacioMecanic> r = new ArrayList<ReparacioMecanic>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
           String str ="SELECT \"OrdreReparacio\",\"Comptador\",\"Observacions\",\"DataAssignacio\",\"DataInici\",\"DataFi\",Ret.\"Estado\","+
                    " V.\"marca\",V.\"tipus\",V.\"num_chasis\",V.\"model\",V.\"matricula\",V.\"color\",V.\"any\" "+
                    " FROM \"Reparacio\" as R"+
                    " INNER JOIN \"ReparacionEstadoTipo\" as Ret on Ret.\"IdReparacionEstadoTipo\"=R.\"IdReparacionEstadoTipo\" "+
                    " INNER JOIN \"Solicitud\" as S on S.\"numSol\"=R.\"numSol\" " +
                    "INNER JOIN \"Client\" as C on C.\"nif\"=s.\"client\" " +
                    " INNER JOIN \"Vehicle\" as V on V.\"IdVehicle\"=C.\"IdVehicle\" ";
                    //" WHERE \"IdM ecanic\"= "+idMec;
            
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
         ReparacioMecanic rm=new ReparacioMecanic();
                Reparacio re = new Reparacio();
                re.setOrdreReparacio(result.getInt("OrdreReparacio"));
                re.setComptador(result.getDouble("Comptador"));                
                re.setObservacions(result.getString("Observacions"));
                re.setEstado(result.getString("Estado"));
                re.setDataAssignacio(result.getDate("DataAssignacio"));
                re.setDataFi(result.getDate("DataFi"));
                re.setDataInici(result.getDate("DataInici"));
                rm.setReparacio(re);
                 Vehicle ve=new Vehicle();
                 ve.setMarca(result.getString("marca"));
                 ve.setTipus(result.getString("tipus"));
                 ve.setNum_chasis(result.getString("num_chasis"));
                 ve.setModel(result.getString("model"));
                 ve.setMatricula(result.getString("matricula"));
                 ve.setColor(result.getString("color"));
                 ve.setAnio(result.getDate("any"));
                rm.setVehicle(ve);
                r.add(rm);
        
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return r;   
    }

    @Override
    public ArrayList<ReparacioMecanic> getReparacionsByMecanic(int idMec) throws PAC4Exception, RemoteException, SQLException {
         ArrayList<ReparacioMecanic> r = new ArrayList<ReparacioMecanic>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str ="SELECT \"OrdreReparacio\",\"Comptador\",\"Observacions\",\"DataAssignacio\",\"DataInici\",\"DataFi\",Ret.\"Estado\","+
                    " V.\"marca\",V.\"tipus\",V.\"num_chasis\",V.\"model\",V.\"matricula\",V.\"color\",V.\"any\" "+
                    " FROM \"Reparacio\" as R"+
                    " INNER JOIN \"ReparacionEstadoTipo\" as Ret on Ret.\"IdReparacionEstadoTipo\"=R.\"IdReparacionEstadoTipo\" "+
                    " INNER JOIN \"Solicitud\" as S on S.\"numSol\"=R.\"numSol\" "+
                    "INNER JOIN \"Client\" as C on C.\"nif\"=s.\"client\" " +
                    " INNER JOIN \"Vehicle\" as V on V.\"IdVehicle\"=C.\"IdVehicle\" "+
                    " WHERE \"IdMecanic\"= "+idMec;
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
                ReparacioMecanic rm=new ReparacioMecanic();
                Reparacio re = new Reparacio();
                re.setOrdreReparacio(result.getInt("OrdreReparacio"));
                re.setComptador(result.getDouble("Comptador"));                
                re.setObservacions(result.getString("Observacions"));
                re.setEstado(result.getString("Estado"));
                re.setDataFi(result.getDate("DataAssignacio"));
                re.setDataFi(result.getDate("DataFi"));
                re.setDataInici(result.getDate("DataInici"));
                rm.setReparacio(re);
                 Vehicle ve=new Vehicle();
                 ve.setMarca(result.getString("marca"));
                 ve.setTipus(result.getString("tipus"));
                 ve.setNum_chasis(result.getString("num_chasis"));
                 ve.setModel(result.getString("model"));
                 ve.setMatricula(result.getString("matricula"));
                 ve.setColor(result.getString("color"));
                 ve.setAnio(result.getDate("any"));
                rm.setVehicle(ve);
                r.add(rm);
        
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return r;   
    }

    @Override
    public ArrayList<SolicitudDetall> getSolicituds() throws PAC4Exception, RemoteException, SQLException {
         ArrayList<SolicitudDetall> s = new ArrayList<SolicitudDetall>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str ="SELECT * "+
                        "from \"Solicitud\" as S "+
                        "INNER JOIN \"SolicitudEstadoTipo\" as Set ON Set.\"IdSolicitudEstadoTipo\"=S.\"IdSolicitudEstadoTipo\" "+
                        "INNER JOIN \"Client\" as C on C.\"nif\"=s.\"client\" "+ 
                        "INNER JOIN \"Vehicle\" as V on V.\"IdVehicle\"=C.\"IdVehicle\" "+
                        //"WHERE \"dataFinalitzacio\"  is null ";
                         "WHERE S.\"IdSolicitudEstadoTipo\"=1;";
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
                SolicitudDetall sd=new SolicitudDetall();
                Solicitud so = new Solicitud();
                so.setAsseguradora(result.getString("Asseguradora"));
                so.setClient(result.getString("Client"));                
                so.setComentaris(result.getString("Comentaris"));
                so.setDataAlta(result.getDate("DataAlta"));
                so.setDataFinalitzacio(result.getDate("DataFinalitzacio"));
                so.setIdSolicitudEstadoTipo(result.getInt("IdSolicitudEstadoTipo"));
                so.setNumPoliza(result.getString("NumPoliza"));
                so.setNumSol(result.getInt("numsol"));
                sd.setSol(so);
                sd.setEstado(result.getString("Estado"));
                Client c = new Client();
                c.setAdresa(result.getString("Adreça"));
                c.setCodiPostal(result.getInt("CodiPostal"));                
                c.setCognoms(result.getString("Cognoms"));
                c.setDataAlta(result.getDate("DataAlta"));
                c.setIdVehicle(result.getInt("IdVehicle"));
                c.setNif(result.getString("Nif"));
                c.setNom(result.getString("Nom"));
                c.setNumCliente(result.getInt("NumClient"));
                c.setPoblacio(result.getString("Poblacio"));
                sd.setClient(c);
                
                 Vehicle ve=new Vehicle();
                 ve.setMarca(result.getString("marca"));
                 ve.setTipus(result.getString("tipus"));
                 ve.setNum_chasis(result.getString("num_chasis"));
                 ve.setModel(result.getString("model"));
                 ve.setMatricula(result.getString("matricula"));
                 ve.setColor(result.getString("color"));
                 ve.setAnio(result.getDate("any"));
                sd.setVehicle(ve);
                s.add(sd);
        
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return s;   
        

    }
    private int updateSolicitudFromReparacion(int IdSolicitud, int Estado) throws PAC4Exception, RemoteException, SQLException {
        
       PreparedStatement st = null;
        
        try {
            
            
        String query = "UPDATE \"Solicitud\" "+
                        " SET \"IdSolicitudEstadoTipo\"=?"+
                        " WHERE \"numSol\"="+IdSolicitud+";";
            
            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, Estado); 
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            
           int IdSolicitut = 1;
            return IdSolicitut;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
            
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }      
        
    }
    @Override
    public int insertReparacionSolicitud(int IdSolicitud, int IdCapTaller, int Accion) throws PAC4Exception, RemoteException, SQLException {
        int IdReparacion=updateSolicitudFromReparacion(IdSolicitud,Accion);
        if(Accion==2)
        {
       PreparedStatement st = null;
        
        try {
            //int IdReparacion = 1;
            
            String query = "INSERT INTO \"Reparacio\" (\"OrdreReparacio\", \"IdCapTaller\", \"IdMecanic\", \"Comptador\", \"Observacions\", " +
                           " \"NumCom\", \"DataAssignacio\", \"DataInici\", \"DataFi\", \"IdReparacionEstadoTipo\",\"numSol\")" +
                           " VALUES (?, ?, null, 0, null, ?, null, CURRENT_DATE, null, 1, ?)";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, IdSolicitud); 
            st.setInt(2, IdCapTaller); 
            st.setInt(3, 1); //pendiente numcom
            st.setInt(4, IdSolicitud); 
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            /*if (rs.next())
                IdReparacion = rs.getInt(9);*/
            
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }   
        }
        return IdReparacion;
        
    }
    private int insertMecanicReparacion(int IdReparacio, int IdMecanic) throws PAC4Exception, RemoteException, SQLException {
        
        
       PreparedStatement st = null;
        
        try {
            int IdReparacion = 1;
            
            String query = "INSERT INTO \"Mecanic\"(\"IdMecanic\", \"Disponible\", \"IdRep1\", \"IdRep2\")"+
                          " VALUES (?,TRUE , ?, ?);";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, IdMecanic); 
            st.setInt(2, IdReparacio); 
            st.setInt(3, IdReparacio); 
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            /*if (rs.next())
                IdReparacion = rs.getInt(9);*/
            
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }   
        
        
    }
    private int deleteMecanicReparacion(int IdReparacio, int IdMecanic) throws PAC4Exception, RemoteException, SQLException {
        
        
       PreparedStatement st = null;
        
        try {
            int IdReparacion = 1;
            
            String query = "DELETE FROM \"Mecanic\" WHERE \"IdMecanic\"=? AND (\"IdRep1\"=? OR \"IdRep2\"=?);";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, IdMecanic); 
            st.setInt(2, IdReparacio); 
            st.setInt(3, IdReparacio); 
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            /*if (rs.next())
                IdReparacion = rs.getInt(9);*/
            
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }   
    }
    @Override
    public int UpdateMecanicoReparacion(Reparacio rep) throws PAC4Exception, RemoteException, SQLException {
       
        int res=this.insertMecanicReparacion(rep.getOrdreReparacio(), rep.getIdMecanic());
        if(res==1)
        {
       PreparedStatement st = null;
        
        try {
            int IdReparacion = 1;
            
            String query = "UPDATE \"Reparacio\" " +
                           " SET \"IdMecanic\"=?,  " +
                           " \"DataAssignacio\"=CURRENT_DATE , " +
                           " \"IdReparacionEstadoTipo\"=2 " +
                           " WHERE \"OrdreReparacio\"="+rep.getOrdreReparacio()+";";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, rep.getIdMecanic()); 
      
  
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
           //if (rs.next())
              //  IdReparacion = 1;
            
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }   
        }
        return 0;
    }

    @Override
    public int UpdateObservacionReparacion(Reparacio rep) throws PAC4Exception, RemoteException, SQLException {
      PreparedStatement st = null;
        
        try {
            int IdReparacion = 0;
            
            String query = "UPDATE \"Reparacio\" " +
                           " SET \"Observacions\"=?  " +
                           
                           " WHERE \"OrdreReparacio\"="+rep.getOrdreReparacio()+";";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setString(1, rep.getObservacions()); 
      
  
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
           if (rs.next())
                IdReparacion = 1;
            
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }     
    }

    @Override
    public int UpdateHorasReparacion(Reparacio rep) throws PAC4Exception, RemoteException, SQLException {
        PreparedStatement st = null;
        
        try {
            int IdReparacion = 0;
            
            String query = "UPDATE \"Reparacio\" " +
                           " SET \"Comptador\"=?  " +
                           
                           " WHERE \"OrdreReparacio\"="+rep.getOrdreReparacio()+";";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setDouble(1, rep.getComptador()); 
      
  
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
           if (rs.next())
                IdReparacion = 1;
            
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }

    @Override
    public int FinalizarReparacion(Reparacio rep) throws PAC4Exception, RemoteException, SQLException {
        int result=FinalizarSolicitudFromReparacion(rep.getOrdreReparacio(),4);
        
            
        if(result==1){    
        PreparedStatement st = null;
        
        try {
            int IdReparacion =1;
            
            String query = "UPDATE \"Reparacio\" " +
                           " SET \"IdReparacionEstadoTipo\"=?, \"DataFi\"=CURRENT_DATE " +                           
                           " WHERE \"OrdreReparacio\"="+rep.getOrdreReparacio()+";";

            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setDouble(1, 3); 
      
  
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
           //if (rs.next())
             //   IdReparacion = 1;
            //Eliminar mecanico
            //IdReparacion=deleteMecanicReparacion(rep.getOrdreReparacio(),rep.getIdMecanic());
            return IdReparacion;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        
    }
        return 0;
    }

    private int FinalizarSolicitudFromReparacion(int IdSolicitud, int Estado) throws RemoteException {
         
        
       PreparedStatement st = null;
        
        try {
            
            
        String query = "UPDATE \"Solicitud\" "+
                        " SET \"IdSolicitudEstadoTipo\"=?, \"dataFinalitzacio\"=CURRENT_DATE"+
                        " WHERE \"numSol\"="+IdSolicitud+";";
            
            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            st.setInt(1, Estado); 
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            
           int IdSolicitut = 1;
            return IdSolicitut;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
            
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }      
        
    
    }

    @Override
    public ArrayList<Peca> GetComandaPeces(Reparacio rep) throws PAC4Exception, RemoteException, SQLException {
        ArrayList<Peca> m = new ArrayList<Peca>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
           String str ="SELECT \"NumCom\", \"fechaPeticion\", P.\"CodiPeca\",P.\"Descripcio\", P.\"IdProveidor\", \"fechaEntrega\",\"PVP\", \"PVD\", \"Marca\", \"Model\" " +
"  FROM \"Comanda\" As C" +
"  INNER JOIN \"Peca\" As P ON P.\"CodiPeca\"=c.\"CodiPeca\"" +
"  WHERE \"OrdreReparacio\"="+rep.getOrdreReparacio();
            
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
         
                Peca me = new Peca();
                me.setCodiPeca(result.getInt("CodiPeca"));
                me.setDescripcio(result.getString("Descripcio"));                
                me.setIdProveidor(result.getInt("IdProveidor"));                
                me.setMarca(result.getString("Marca"));                
                me.setModel(result.getString("Model"));                
                me.setPvd(result.getInt("Pvd"));                
                me.setPvp(result.getInt("Pvp"));                
                
                m.add(me);
        
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return m; 
    }

    @Override
    public int AddPecaComanda(Reparacio rep, Peca peca) throws PAC4Exception, RemoteException, SQLException {
        
 PreparedStatement st = null;
        
        try {
            
            
        String query =  "INSERT INTO \"Comanda\"(" +
                        " \"NumCom\", \"Estat\", \"fechaPeticion\", \"CodiPeca\", \"IdCapTaller\", " +
                        "  \"IdProveidor\", \"OrdreReparacio\", \"TipusReparacio\", \"fechaEntrega\")" +
                        " VALUES ((SELECT MAX(\"NumCom\")+1 FROM \"Comanda\"), TRUE, CURRENT_DATE, ?, ?, ?, ?, TRUE, NULL);";
            
            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
//            TODO:pendiente autoincremental
            
            
            st.setInt(1, peca.getCodiPeca()); 
            st.setInt(2, rep.getIdCapTaller()); 
            st.setInt(3, peca.getIdProveidor()); 
            st.setInt(4, rep.getOrdreReparacio()); 
            
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            
           int IdSolicitut = 1;
            return IdSolicitut;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
            
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }  
    }

    @Override
    public int DelPecaComanda(Reparacio rep, Peca peca) throws PAC4Exception, RemoteException, SQLException {
        PreparedStatement st = null;
        
        try {
            
            
        String query =  "DELETE FROM \"Comanda\"" +
                        " WHERE \"OrdreReparacio\"=? AND \"CodiPeca\"=?;";
            
            st = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
//            TODO:pendiente autoincremental
            st.setInt(1, rep.getOrdreReparacio()); 
            st.setInt(2, peca.getCodiPeca()); 
            
            st.executeUpdate();
            
            ResultSet rs = st.getGeneratedKeys();
            
           int IdSolicitut = 1;
            return IdSolicitut;
            
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos          
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
            
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
    }

    @Override
    public ArrayList<Peca> getPeces() throws PAC4Exception, RemoteException, SQLException {
        ArrayList<Peca> m = new ArrayList<Peca>();
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
           String str ="SELECT \"CodiPeca\", \"Descripcio\", \"IdProveidor\" " +
                       "  FROM \"Peca\";";
            
            result = st.executeQuery(str);
            System.out.println(str);
            
            while(result.next()) {
         
                Peca me = new Peca();
                me.setCodiPeca(result.getInt("CodiPeca"));
                me.setDescripcio(result.getString("Descripcio"));                
                me.setIdProveidor(result.getInt("IdProveidor"));
                
                
                m.add(me);
        
            }
        } catch (SQLException e) {
            //No se pudo conectar con la base de datos  
            throw new RemoteException(TDSLanguageUtils.getMessage("jdbc.conexion.bd.error.no_existe"));
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                //ExcepciÃ³n inesperada  
                throw new RemoteException(TDSLanguageUtils.getMessage("error.excepcion.no_esperada"));
            }
        }
        return m;   
    }
    
    
  
    
}
